package com.deng.etl.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.deng.etl.common.EtlAuto;
import com.deng.etl.common.IEtlInterface;
import com.deng.etl.common.EtlLog;

//清空删除

public class ImportCostDetails extends ImportBase implements IEtlInterface {
	
	public static String COLUMNS="dept_code,dept_name,cost_subj_code," +
			"cost_subj_name,prime_cost,year_month+'-'+dept_code+'-'+cost_subj_code as id";
	
	public static String SELECT="select top "+TOPN+" "+COLUMNS+"  from VIEW_JXKSZD";
	
	public static String INSERT="insert into t_org_operate_item_count_month" +
			"(operate_code,operate_name,item_code,item_id,item_name," +
			"stat_time,chengben_type,fee,create_time) values(?,?,?,null,?,?,null,?,?)";
	
	public static String DELETE="delete from t_org_operate_item_count_month where stat_time>=? and stat_time<? ";	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

		
	}
	
	public int getResultSet(ResultSet rs,String arr,int size){
		if(size<TOPN){
			return size;
		}
		int ret=0;
		String pri="";
		try {
			for(int i=size;i>0;i--){
				rs.absolute(i);
				if(!pri.equals(rs.getString(arr))&&i!=size){
					ret=i;
					break;
				}
				
				pri=rs.getString(arr);
			}
			rs.beforeFirst();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}
	
	
	public void insertData(Connection sourceConn,Connection targetConn,String name,String fromDate,String toDate) {
		System.out.println("从："+fromDate+"   到："+toDate);
		System.out.println("sourceConn = targetConn");
		sourceConn = new EtlAuto().getSourceChengBenConn();
		Date from=null;
		Date to=null;
		try {
			from = format_s.parse(fromDate);
			to=format_s.parse(toDate);
		} catch (ParseException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		SimpleDateFormat format=new SimpleDateFormat("yyyyMM");
		
		String sql=SELECT+" where year_month>='"+format.format(from)+"' and year_month<'"+format.format(to)+"' and year_month+'-'+dept_code+'-'+cost_subj_code >? " +
		"order by id ";
		System.out.println(sql);
		try {
			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
			PreparedStatement pst=sourceConn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			
			PreparedStatement pst_insert=targetConn.prepareStatement(INSERT);
			
			String index="0";
			int sum=0;
		
			while(true){
				pst.setString(1, index);
				ResultSet rs=pst.executeQuery();
				rs.last();
				int size=rs.getRow();
				rs.beforeFirst();

				int i=getResultSet(rs,"id",size);
				
				int n=0;
				while(rs.next()&&n<i){
					sum++;
					n++;
					String id=rs.getString("id");
					System.out.println("sum=="+sum+";id="+id);

					String item_code=rs.getString("cost_subj_code");
					String item_name=rs.getString("cost_subj_name");
					String operate_code=rs.getString("dept_code");
					String operate_name=rs.getString("dept_name");
					double cost=rs.getDouble("prime_cost");
					
					java.sql.Date date=new java.sql.Date(from.getYear(),from.getMonth(),1);
					if(n==i){
						index=id;
					}
					pst_insert.setString(1, operate_code);
					pst_insert.setString(2, operate_name);
					pst_insert.setString(3, item_code);
					pst_insert.setString(4, item_name);
					pst_insert.setDate(5, date);
					pst_insert.setDouble(6, cost);
					Date datetmp=new Date();
					pst_insert.setTimestamp(7, getTimestamp(datetmp));
//					insert(pst_insert);
						
					pst_insert.addBatch();

				}
				pst_insert.executeBatch();
				rs.close();
				if(size<TOPN)
					break;
			}
			
			System.out.println("sum="+sum);
			pst.close();
			pst_insert.close();
//			EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void deleteData(Connection sourceConn, Connection targetConn,
			String name, String fromDate, String toDate) {
		// TODO Auto-generated method stub
		System.out.println("从："+fromDate+"   到："+toDate);
		sourceConn = new EtlAuto().getSourceChengBenConn();
		Date from=null;
		Date to=null;

		try {
			from = format_s.parse(fromDate);
			to = format_s.parse(toDate);
		} catch (ParseException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try{
			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
			PreparedStatement pst_delete=targetConn.prepareStatement(DELETE);
			java.sql.Date date=new java.sql.Date(from.getYear(),from.getMonth(),1);
			
			java.sql.Date date1=new java.sql.Date(to.getYear(),to.getMonth(),1);
			pst_delete.setDate(1,date);
			pst_delete.setDate(2,date1);
			pst_delete.executeUpdate();
			pst_delete.close();
				
//			EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
		}
		catch(SQLException e){
			
		}
		
	}

	public void updateData(Connection sourceConn, Connection targetConn,
			String name, String fromDate, String toDate) {
		try{
			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
			sourceConn = new EtlAuto().getSourceChengBenConn();
			deleteData(sourceConn, targetConn,
					name, fromDate, toDate);
			insertData(sourceConn, targetConn,
					name, fromDate, toDate);
			
//			EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		
	}
}
